﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{

    public class ISSUEGREIGEData : BaseFunction
    {               
        string TrisInclude = "";
        public int ThreadCount;
        private string OrderNum;
        public ISSUEGREIGEData()
            : base()
        {
            BuildTrisList();  
        }
        public ISSUEGREIGEData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }
        private void BuildTrisList()
        {
            

        }
   
        public DataSet GetRawData(DataRow dwABPOS)
        {
            DataSet set4Return = new DataSet();
            DataSet setLocal = new DataSet();
            DataSet setResult = new DataSet();
            DataSet setTemp = new DataSet();
            DataTable tableLocal = null;
            string WBSRTDATE, WBENTDATE ;
            WBSRTDATE = dwABPOS["WBSRTDATE"].ToString().Trim();
            WBENTDATE = dwABPOS["WBENTDATE"].ToString().Trim();
            try
            {
                OracleCommand or1 = OracleDB.CreateCommand();
                #region sql command
                /*"SELECT ARTICLE,BATCHNO,MTR,0 as WMTR,WORDERNO,WFACTORY,FORDERNO,FFACTORY,COLORDES, "
                    + " BATCHQTY,BATCHMTR,FINISHQTY,FINISHMTR,UNCHECKEDQTY,UNCHECKEDMTR,CHECKEDQTY,CHECKEDMTR, "
                    + " NVL(UNCHECKEDQTY,0) + NVL(CHECKEDQTY,0) AS TOTALQTY,NVL(UNCHECKEDMTR,0) + NVL(CHECKEDMTR,0) AS TOTALMTR, "
                    + " '' AS REMARK,FART,FVAR,FCOLOR,'' AS COMPOSITION,'' AS WAFBRTL,'' AS JS,'' AS WS,'' AS PRICE,'' AS RATE "
                    + " FROM (select TRIM(A.WBBART)||'-'||TRIM(A.WBBFARBE)||'-'||A.WBBVAR AS ARTICLE, "
                    + " TRIM(A.WBBDPART) AS BATCHNO,B.MTR,B.WORDERNO,B.WFACTORY,B.FORDERNO,B.FFACTORY,B.COLORDES, "
                    + " FART,FVAR,FCOLOR,C.BATCHQTY,C.BATCHMTR,D.FINISHQTY,D.FINISHMTR, "
                    + " SUM((SELECT 1 FROM WBBST C WHERE A.WBBMANDANT = C.WBBMANDANT "
                    + " AND A.WBBRWKRS = C.WBBRWKRS AND A.WBBRWLFD = C.WBBRWLFD AND A.WBBPOS = C.WBBPOS "
                    + " AND C.WBBLO='HL' AND C.WBBLB = '30')) AS UNCHECKEDQTY, "
                    + " TRUNC(SUM((SELECT C.WBBISTKLG/1000 FROM WBBST C WHERE A.WBBMANDANT = C.WBBMANDANT "
                    + " AND A.WBBRWKRS = C.WBBRWKRS AND A.WBBRWLFD = C.WBBRWLFD AND A.WBBPOS = C.WBBPOS "
                    + " AND C.WBBLO='HL' AND C.WBBLB = '30')),2) AS UNCHECKEDMTR, "
                    + " SUM((SELECT 1 FROM WBBST C WHERE A.WBBMANDANT = C.WBBMANDANT "
                    + " AND A.WBBRWKRS = C.WBBRWKRS AND A.WBBRWLFD = C.WBBRWLFD AND A.WBBPOS = C.WBBPOS "
                    + " AND C.WBBLO='HL' AND C.WBBLB = '20')) AS CHECKEDQTY, "
                    + " TRUNC(SUM((SELECT C.WBBISTKLG/1000 FROM WBBST C WHERE A.WBBMANDANT = C.WBBMANDANT "
                    + " AND A.WBBRWKRS = C.WBBRWKRS AND A.WBBRWLFD = C.WBBRWLFD AND A.WBBPOS = C.WBBPOS "
                    + " AND C.WBBLO='HL' AND C.WBBLB = '20')),2) AS CHECKEDMTR "
                    + " from wbbst a,(SELECT DISTINCT SIZINGLOT,MTR,WFACTORY,ABPART,ABPVAR,ABPFARBE,FART,FVAR,FCOLOR, "
                    + " WORDERNO,FORDERNO,FFACTORY,COLORDES FROM ( SELECT DISTINCT B.BMBEM AS SIZINGLOT,B.MTR,TRIM(D2.LSNAME1) AS WFACTORY, "
                    + " D.ABPART,TRIM(D.ABPVAR) AS ABPVAR ,D.ABPFARBE,F.ABPART AS FART,F.ABPVAR AS FVAR,F.ABPFARBE AS FCOLOR, "
                    + " DECODE(D.ABPAUFKRS,'PB', "
                    + " (SELECT T.ABADIAUFKRS||'-'||TO_NUMBER(T.ABADIAUFLFD)||'-'||TO_NUMBER(T.ABADIAUFPOS) "
                    + " FROM ABAUFM T WHERE T.ABAMANDANT = D.ABPMANDANT AND T.ABAAUFKRS = D.ABPAUFKRS "
                    + " AND T.ABAAUFLFD = D.ABPAUFLFD AND T.ABAAUFPOS = D.ABPAUFPOS "
                    + " AND T.ABAAPOS = '0001'), "
                    + " D.ABPAUFKRS||'-'||TO_NUMBER(D.ABPAUFLFD)||'-'||TO_NUMBER(D.ABPAUFPOS)) AS WORDERNO, "
                    + " F.ABPAUFKRS||'-'||TO_NUMBER(F.ABPAUFLFD)||'-'||TO_NUMBER(F.ABPAUFPOS) AS FORDERNO, "
                    + " TRIM(F2.LSNAME1) AS FFACTORY,NVL(TRIM(G.COLORDES),F.ABPBESCHICH) AS COLORDES "
                    + " FROM (SELECT BMMANDANT,BMAUFKRS,BMAUFLFD,BMAUFPOS,TRIM(BMBEM) AS BMBEM, "
                    + " SUM(BMISTLG/10)AS MTR from BABST "
                    + " GROUP BY BMMANDANT,BMAUFKRS,BMAUFLFD,BMAUFPOS,TRIM(BMBEM)) B, "
                    + " ABAUFM C,ABPOS D,ABAUFM E,ABPOS F, "
                    + " (SELECT DISTINCT ABFMANDANT,ABFAUFKRS,ABFAUFLFD,ABFAUFPOS,ABFLIEFNR "
                    + " FROM ABFOLGE WHERE TRIM(ABFLIEFNR) IS NOT NULL) D1,LIEFDAT D2, "
                    + " (SELECT DISTINCT ABFMANDANT,ABFAUFKRS,ABFAUFLFD,ABFAUFPOS,ABFLIEFNR "
                    + " FROM ABFOLGE WHERE TRIM(ABFLIEFNR) IS NOT NULL) F1,LIEFDAT F2, "
                    + " (SELECT DISTINCT SUBSTR(SDKEY,3,10) as COLOR, "
                    + " SUBSTR(SDDATEN,2,30) AS COLORDES FROM SDDAT "
                    + " WHERE SUBSTR(SDKEY,1,2)='00' AND SDSA = '9998') G "
                    + " WHERE B.BMMANDANT = C.ABAMANDANT "
                    + " AND B.BMAUFKRS = C.ABAAUFKRS AND B.BMAUFLFD = C.ABAAUFLFD "
                    + " AND B.BMAUFPOS = C.ABAAUFPOS AND C.ABAAPOS >'0001' "
                    + " AND C.ABAMANDANT = D.ABPMANDANT AND C.ABAVKAUFKRS = D.ABPAUFKRS "
                    + " AND C.ABAVKAUFLFD = D.ABPAUFLFD AND C.ABAVKAUFPOS = D.ABPAUFPOS "
                    + " AND D.ABPMANDANT = D1.ABFMANDANT AND D.ABPAUFKRS = D1.ABFAUFKRS "
                    + " AND D.ABPAUFLFD = D1.ABFAUFLFD AND D.ABPAUFPOS = D1.ABFAUFPOS "
                    + " AND D1.ABFMANDANT = D2.LSMANDANT AND D1.ABFLIEFNR = D2.LSLIEFNR "
                    + " AND D.ABPMANDANT = E.ABAMANDANT AND D.ABPAUFKRS = E.ABAAUFKRS "
                    + " AND D.ABPAUFLFD = E.ABAAUFLFD AND D.ABPAUFPOS = E.ABAAUFPOS "
                    + " AND E.ABAAPOS > '0001'  AND E.ABAMANDANT = F.ABPMANDANT "
                    + " AND E.ABAVKAUFKRS = F.ABPAUFKRS AND E.ABAVKAUFLFD = F.ABPAUFLFD "
                    + " AND E.ABAVKAUFPOS = F.ABPAUFPOS AND F.ABPMANDANT = F1.ABFMANDANT "
                    + " AND F.ABPAUFKRS = F1.ABFAUFKRS AND F.ABPAUFLFD = F1.ABFAUFLFD "
                    + " AND F.ABPAUFPOS = F1.ABFAUFPOS AND F1.ABFMANDANT = F2.LSMANDANT "
                    + " AND F1.ABFLIEFNR = F2.LSLIEFNR AND F.ABPBESCHICH = G.COLOR(+) "
                    + " AND F.ABPERLKZ <> 'J' AND B.MTR >0 AND EXISTS (SELECT '1' "
                    + " FROM BABEW W1,BABST T1,ABAUFM A1 WHERE W1.BBMANDANT = T1.BMMANDANT "
                    + " AND W1.BBBAUMKRS = T1.BMBAUMKRS AND W1.BBBAUMLFD = T1.BMBAUMLFD "
                    + " AND W1.BBBS = '36' AND W1.BBFC = 'WB15'  "
                    + " AND W1.BBMANDANT = A1.ABAMANDANT AND W1.BBAUFKRS = A1.ABAAUFKRS "
                    + " AND W1.BBAUFLFD = A1.ABAAUFLFD AND W1.BBAUFPOS = A1.ABAAUFPOS "
                    + " AND A1.ABAAPOS = '0001' AND TRIM(T1.BMBEM) = B.BMBEM "
                    + " AND D.ABPAUFKRS = DECODE(W1.BBAUFKRS,'PB',A1.ABADIAUFKRS,W1.BBAUFKRS) "
                    + " AND D.ABPAUFLFD = DECODE(W1.BBAUFKRS,'PB',A1.ABADIAUFLFD,W1.BBAUFLFD) "
                    + " AND D.ABPAUFPOS = DECODE(W1.BBAUFKRS,'PB',A1.ABADIAUFPOS,W1.BBAUFPOS)) "
                    + " UNION ALL "
                    + " SELECT DISTINCT B.BMBEM AS SIZINGLOT,B.MTR,TRIM(D2.LSNAME1) AS WFACTORY, "
                    + " D.ABPART,TRIM(D.ABPVAR) AS ABPVAR,D.ABPFARBE,F.ABPART AS FART,F.ABPVAR AS FVAR,F.ABPFARBE AS FCOLOR, "
                    + " D.ABPAUFKRS||'-'||TO_NUMBER(D.ABPAUFLFD)||'-'||TO_NUMBER(D.ABPAUFPOS) AS WORDERNO, "
                    + " F.ABPAUFKRS||'-'||TO_NUMBER(F.ABPAUFLFD)||'-'||TO_NUMBER(F.ABPAUFPOS) AS FORDERNO, "
                    + " TRIM(F2.LSNAME1) AS FFACTORY,NVL(TRIM(G.COLORDES),F.ABPBESCHICH) AS COLORDES "
                    + " FROM (SELECT BMMANDANT,BMAUFKRS,BMAUFLFD,BMAUFPOS,TRIM(BMBEM) AS BMBEM, "
                    + " SUM(BMISTLG/10)AS MTR from BABST "
                    + " GROUP BY BMMANDANT,BMAUFKRS,BMAUFLFD,BMAUFPOS,TRIM(BMBEM)) B, "
                    + " ABAUFM C,ABPOS D,ABAUFM E,ABPOS F,ABAUFM G, "
                    + " (SELECT DISTINCT ABFMANDANT,ABFAUFKRS,ABFAUFLFD,ABFAUFPOS,ABFLIEFNR "
                    + " FROM ABFOLGE WHERE TRIM(ABFLIEFNR) IS NOT NULL) D1,LIEFDAT D2, "
                    + " (SELECT DISTINCT ABFMANDANT,ABFAUFKRS,ABFAUFLFD,ABFAUFPOS,ABFLIEFNR "
                    + " FROM ABFOLGE WHERE TRIM(ABFLIEFNR) IS NOT NULL) F1,LIEFDAT F2, "
                    + " (SELECT DISTINCT SUBSTR(SDKEY,3,10) as COLOR, "
                    + " SUBSTR(SDDATEN,2,30) AS COLORDES FROM SDDAT "
                    + " WHERE SUBSTR(SDKEY,1,2)='00' AND SDSA = '9998') G "
                    + " WHERE B.BMMANDANT = C.ABAMANDANT AND B.BMAUFKRS = C.ABAAUFKRS AND B.BMAUFLFD = C.ABAAUFLFD "
                    + " AND B.BMAUFPOS = C.ABAAUFPOS AND C.ABAAPOS >'0001' "
                    + " AND C.ABAMANDANT = D.ABPMANDANT AND C.ABAVKAUFKRS = D.ABPAUFKRS "
                    + " AND C.ABAVKAUFLFD = D.ABPAUFLFD AND C.ABAVKAUFPOS = D.ABPAUFPOS "
                    + " AND D.ABPMANDANT = D1.ABFMANDANT AND D.ABPAUFKRS = D1.ABFAUFKRS "
                    + " AND D.ABPAUFLFD = D1.ABFAUFLFD AND D.ABPAUFPOS = D1.ABFAUFPOS "
                    + " AND D1.ABFMANDANT = D2.LSMANDANT AND D1.ABFLIEFNR = D2.LSLIEFNR "
                    + " AND D.ABPMANDANT = G.ABAMANDANT AND D.ABPAUFKRS = G.ABADIAUFKRS "
                    + " AND D.ABPAUFLFD = G.ABADIAUFLFD AND D.ABPAUFPOS = G.ABADIAUFPOS "
                    + " AND G.ABAAPOS = '0001' AND G.ABAMANDANT = E.ABAMANDANT AND G.ABAAUFKRS = E.ABAAUFKRS "
                    + " AND G.ABAAUFLFD = E.ABAAUFLFD AND G.ABAAUFPOS = E.ABAAUFPOS "
                    + " AND E.ABAAPOS > '0001'  AND E.ABAMANDANT = F.ABPMANDANT "
                    + " AND E.ABAVKAUFKRS = F.ABPAUFKRS AND E.ABAVKAUFLFD = F.ABPAUFLFD "
                    + " AND E.ABAVKAUFPOS = F.ABPAUFPOS AND F.ABPMANDANT = F1.ABFMANDANT "
                    + " AND F.ABPAUFKRS = F1.ABFAUFKRS AND F.ABPAUFLFD = F1.ABFAUFLFD "
                    + " AND F.ABPAUFPOS = F1.ABFAUFPOS AND F1.ABFMANDANT = F2.LSMANDANT "
                    + " AND F1.ABFLIEFNR = F2.LSLIEFNR AND F.ABPBESCHICH = G.COLOR(+) "
                    + " AND F.ABPERLKZ <> 'J' AND B.MTR >0   AND EXISTS (SELECT '1' "
                    + " FROM BABEW W1,BABST T1,ABAUFM A1 WHERE W1.BBMANDANT = T1.BMMANDANT "
                    + " AND W1.BBBAUMKRS = T1.BMBAUMKRS AND W1.BBBAUMLFD = T1.BMBAUMLFD "
                    + " AND W1.BBBS = '36' AND W1.BBFC = 'WB15'  "
                    + " AND W1.BBMANDANT = A1.ABAMANDANT AND W1.BBAUFKRS = A1.ABAAUFKRS "
                    + " AND W1.BBAUFLFD = A1.ABAAUFLFD AND W1.BBAUFPOS = A1.ABAAUFPOS "
                    + " AND A1.ABAAPOS = '0001' AND TRIM(T1.BMBEM) = B.BMBEM "
                    + " AND D.ABPAUFKRS = DECODE(W1.BBAUFKRS,'PB',A1.ABADIAUFKRS,W1.BBAUFKRS) "
                    + " AND D.ABPAUFLFD = DECODE(W1.BBAUFKRS,'PB',A1.ABADIAUFLFD,W1.BBAUFLFD) "
                    + " AND D.ABPAUFPOS = DECODE(W1.BBAUFKRS,'PB',A1.ABADIAUFPOS,W1.BBAUFPOS)) )) B, "
                    + " (SELECT C.WBBMANDANT,C.WBBART,C.WBBVAR,C.WBBFARBE,C.WBBDPART, "
                    + " SUM(B.WBEWMTR/1000)  AS BATCHMTR, "
                    + " SUM(CASE WHEN B.WBEWMTR <0 THEN -1 ELSE 1 END ) AS BATCHQTY "
                    + "  FROM WBBEW B,WBBST C "
                    + " WHERE C.WBBMANDANT = B.WBEWMANDANT AND C.WBBRWKRS = B.WBEWRWKRS "
                    + "   AND C.WBBRWLFD = B.WBEWRWLFD AND C.WBBPOS = B.WBEWPOS "
                    + "   AND B.WBEWWAKZ  = 'R' "
                    + "   AND (B.WBEWBS IN ('01','10','11') OR (B.WBEWBS = '13' AND trim(B.WBEWFC) = 'WB15')) "
                    + "   GROUP BY C.WBBMANDANT,C.WBBART,C.WBBVAR,C.WBBFARBE,C.WBBDPART) C, "
                    + "   (SELECT D.WBBMANDANT,D.WBBART,D.WBBVAR,D.WBBFARBE,D.WBBDPART, "
                    + "   SUM(D.WBBSSTKLG/1000)  AS FINISHMTR,COUNT(1) AS FINISHQTY FROM WBBST D "
                    + " WHERE D.WBBWAKZ = 'R' AND D.WBBLO = 'HL' AND D.WBBLB ='EX' AND D.WBBAUFKRS IN ('FB','FS') "
                    + " GROUP BY D.WBBMANDANT,D.WBBART,D.WBBVAR,D.WBBFARBE,D.WBBDPART) D "
                    + " WHERE TRIM(A.WBBDPART) = B.SIZINGLOT(+) "
                    + " AND A.WBBART = B.ABPART(+) AND A.WBBVAR = B.ABPVAR(+) "
                    + " AND A.WBBFARBE = B.ABPFARBE(+)  "
                    + " AND A.WBBMANDANT = C.WBBMANDANT "
                    + " AND A.WBBART = C.WBBART AND A.WBBVAR = C.WBBVAR "
                    + " AND A.WBBFARBE = C.WBBFARBE AND A.WBBDPART = C.WBBDPART "
                    + " AND A.WBBMANDANT = D.WBBMANDANT(+) AND A.WBBART = D.WBBART(+) "
                    + " AND A.WBBVAR = D.WBBVAR(+) AND A.WBBFARBE = D.WBBFARBE(+) "
                    + " AND A.WBBDPART = D.WBBDPART(+) "
                    + " AND A.WBBWAKZ = 'R' AND A.WBBVERSANDKZ = 'N' "
                    + " AND A.WBBISTKLG > 0 AND A.WBBLB IN ('20','30') "
                    + " AND A.WBBEINDAT >='" + WBSRTDATE + "' AND A.WBBEINDAT <='" + WBENTDATE + "' "
                    + " GROUP BY TRIM(A.WBBART)||'-'||TRIM(A.WBBFARBE)||'-'||A.WBBVAR, "
                    + " TRIM(A.WBBDPART),B.MTR,B.WORDERNO,B.WFACTORY,B.FORDERNO,B.FFACTORY,B.COLORDES, "
                    + " C.BATCHQTY,C.BATCHMTR,D.FINISHQTY,D.FINISHMTR,FART,FVAR,FCOLOR ) "  */
                or1.CommandText = "SELECT ARTICLE,BATCHNO,MTR,0 as WMTR,WORDERNO,WFACTORY,FORDERNO,FFACTORY,COLORDES, "
                    + " BATCHQTY,BATCHMTR,FINISHQTY,FINISHMTR,UNCHECKEDQTY,UNCHECKEDMTR,CHECKEDQTY,CHECKEDMTR, "
                    + " NVL(UNCHECKEDQTY,0) + NVL(CHECKEDQTY,0) AS TOTALQTY,NVL(UNCHECKEDMTR,0) + NVL(CHECKEDMTR,0) AS TOTALMTR, "
                    + " '' AS REMARK,FART,FVAR,FCOLOR,'' AS COMPOSITION,'' AS WAFBRTL,'' AS JS,'' AS WS,'' AS PRICE,'' AS RATE "
                    + " FROM (select TRIM(A.WBBART)||'-'||TRIM(A.WBBFARBE)||'-'||A.WBBVAR AS ARTICLE, "
                    + " TRIM(A.WBBDPART) AS BATCHNO,B.MTR,B.WORDERNO,B.WFACTORY,B.FORDERNO,B.FFACTORY,B.COLORDES, "
                    + " FART,FVAR,FCOLOR,C.BATCHQTY,C.BATCHMTR,D.FINISHQTY,D.FINISHMTR, "
                    + " SUM((SELECT 1 FROM WBBST C WHERE A.WBBMANDANT = C.WBBMANDANT "
                    + " AND A.WBBRWKRS = C.WBBRWKRS AND A.WBBRWLFD = C.WBBRWLFD AND A.WBBPOS = C.WBBPOS "
                    + " AND C.WBBLO='HL' AND C.WBBLB = '30')) AS UNCHECKEDQTY, "
                    + " TRUNC(SUM((SELECT C.WBBISTKLG/1000 FROM WBBST C WHERE A.WBBMANDANT = C.WBBMANDANT "
                    + " AND A.WBBRWKRS = C.WBBRWKRS AND A.WBBRWLFD = C.WBBRWLFD AND A.WBBPOS = C.WBBPOS "
                    + " AND C.WBBLO='HL' AND C.WBBLB = '30')),2) AS UNCHECKEDMTR, "
                    + " SUM((SELECT 1 FROM WBBST C WHERE A.WBBMANDANT = C.WBBMANDANT "
                    + " AND A.WBBRWKRS = C.WBBRWKRS AND A.WBBRWLFD = C.WBBRWLFD AND A.WBBPOS = C.WBBPOS "
                    + " AND C.WBBLO='HL' AND C.WBBLB = '20')) AS CHECKEDQTY, "
                    + " TRUNC(SUM((SELECT C.WBBISTKLG/1000 FROM WBBST C WHERE A.WBBMANDANT = C.WBBMANDANT "
                    + " AND A.WBBRWKRS = C.WBBRWKRS AND A.WBBRWLFD = C.WBBRWLFD AND A.WBBPOS = C.WBBPOS "
                    + " AND C.WBBLO='HL' AND C.WBBLB = '20')),2) AS CHECKEDMTR "
                    + " from wbbst a,ISSUE_GREIGE B, "
                    + " (SELECT C.WBBMANDANT,C.WBBART,C.WBBVAR,C.WBBFARBE,C.WBBDPART, "
                    + " SUM(B.WBEWMTR/1000)  AS BATCHMTR, "
                    + " SUM(CASE WHEN B.WBEWMTR <0 THEN -1 ELSE 1 END ) AS BATCHQTY "
                    + "  FROM WBBEW B,WBBST C "
                    + " WHERE C.WBBMANDANT = B.WBEWMANDANT AND C.WBBRWKRS = B.WBEWRWKRS "
                    + "   AND C.WBBRWLFD = B.WBEWRWLFD AND C.WBBPOS = B.WBEWPOS "
                    + "   AND B.WBEWWAKZ  = 'R' "
                    + "   AND (B.WBEWBS IN ('01','10','11') OR (B.WBEWBS = '13' AND trim(B.WBEWFC) = 'WB15')) "
                    + "   GROUP BY C.WBBMANDANT,C.WBBART,C.WBBVAR,C.WBBFARBE,C.WBBDPART) C, "
                    + "   (SELECT D.WBBMANDANT,D.WBBART,D.WBBVAR,D.WBBFARBE,D.WBBDPART, "
                    + "   SUM(D.WBBSSTKLG/1000)  AS FINISHMTR,COUNT(1) AS FINISHQTY FROM WBBST D "
                    + " WHERE D.WBBWAKZ = 'R' AND D.WBBLO = 'HL' AND D.WBBLB ='EX' AND D.WBBAUFKRS IN ('FB','FS') "
                    + " GROUP BY D.WBBMANDANT,D.WBBART,D.WBBVAR,D.WBBFARBE,D.WBBDPART) D "
                    + " WHERE TRIM(A.WBBDPART) = B.SIZINGLOT(+) "
                    + " AND A.WBBART = B.ABPART(+) AND A.WBBVAR = B.ABPVAR(+) "
                    + " AND A.WBBFARBE = B.ABPFARBE(+)  "
                    + " AND A.WBBMANDANT = C.WBBMANDANT "
                    + " AND A.WBBART = C.WBBART AND A.WBBVAR = C.WBBVAR "
                    + " AND A.WBBFARBE = C.WBBFARBE AND A.WBBDPART = C.WBBDPART "
                    + " AND A.WBBMANDANT = D.WBBMANDANT(+) AND A.WBBART = D.WBBART(+) "
                    + " AND A.WBBVAR = D.WBBVAR(+) AND A.WBBFARBE = D.WBBFARBE(+) "
                    + " AND A.WBBDPART = D.WBBDPART(+) "
                    + " AND A.WBBWAKZ = 'R' AND A.WBBVERSANDKZ = 'N' "
                    + " AND A.WBBISTKLG > 0 AND A.WBBLB IN ('20','30') "
                    + " AND A.WBBEINDAT >='" + WBSRTDATE + "' AND A.WBBEINDAT <='" + WBENTDATE + "' "
                    + " GROUP BY TRIM(A.WBBART)||'-'||TRIM(A.WBBFARBE)||'-'||A.WBBVAR, "
                    + " TRIM(A.WBBDPART),B.MTR,B.WORDERNO,B.WFACTORY,B.FORDERNO,B.FFACTORY,B.COLORDES, "
                    + " C.BATCHQTY,C.BATCHMTR,D.FINISHQTY,D.FINISHMTR,FART,FVAR,FCOLOR ) "                   
                     ;
                #endregion
                setLocal = Connect2Proxy(or1);
                if (setLocal.Tables.Count > 0)
                {
                    tableLocal = setLocal.Tables[0];
                    tableLocal.TableName = "ISSUEGREIGE";
                    SetDefault(tableLocal);
                    byte[] b;
                    string value;
                    string Column = "";
                    List<DataRow> dltRows = new List<DataRow>(); 
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        Column = "WFACTORY";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        Column = "FFACTORY";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        Column = "COLORDES";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        //GetREMARK(dwTemp);
                        GetREMARK1(dwTemp);
                        GetWASTAMM(dwTemp);
                        GetABPR(dwTemp);
                    } 
                    set4Return.Merge(setLocal); 
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return set4Return;
        }

        private void GetWASTAMM(DataRow dwABPOS)
        {
            string TableName = "WASTAMM";
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT distinct A.WAART,A.WAVAR, "
                    + " ((A.WAFBRTL - A.WAFBRTLT) * 0.1)||'-'||((A.WAFBRTL + A.WAFBRTLT)*0.1) AS WAFBRTL, "
                    + " A.WALAEAEND/100||'%' AS WALAEAEND,B.COMPOSITION "
                    + " FROM WASTAMM A,FABRIC_COMPOSITION B "
                    + " WHERE A.WAMANDANT = B.STUELIMANDANT AND A.WAART = B.WAART AND A.WAVAR = B.WAVAR "
                    + " AND trim(A.WAART) = :WAART AND TRIM(A.WAVAR)=:WAVAR AND trim(B.TYPE) = 'F' "
                     ;
                orlReadCommand.Parameters.Add("WAART", OracleType.NVarChar).Value = dwABPOS["FART"].ToString().Trim();
                orlReadCommand.Parameters.Add("WAVAR", OracleType.NVarChar).Value = dwABPOS["FVAR"].ToString().Trim();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;

                    byte[] b;
                    string value;
                    foreach (DataRow dw in dsResult.Tables[TableName].Rows)
                    {
                        foreach (DataColumn dc in dsResult.Tables[TableName].Columns)
                        {
                            if (dc.ColumnName == "COMPOSITION")
                            {
                                b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[dc.ColumnName].ToString().Trim());
                                value = Encoding.GetEncoding("gb2312").GetString(b);
                                dw[dc.ColumnName] = value;
                            }
                        }
                        dwABPOS["WAFBRTL"] = dw["WAFBRTL"];
                        dwABPOS["RATE"] = dw["WALAEAEND"];
                        dwABPOS["COMPOSITION"] = dw["COMPOSITION"];
                    }
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void GetREMARK1(DataRow dwABPOS)
        {
            string BATCH;
            BATCH = dwABPOS["FINISHQTY"].ToString().Trim();
            if (BATCH =="")
            {
                dwABPOS["REMARK"] = "新缸;";
            }
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT A.ABPERLKZ,ROUND(A.ABPSOLLMENGE1*0.01) AS QTY FROM ABPOS A "
                    + " WHERE A.ABPAUFKRS||'-'||TO_NUMBER(A.ABPAUFLFD)||'-'||TO_NUMBER(A.ABPAUFPOS) = :WORDERNO "
                     ;
                orlReadCommand.Parameters.Add("WORDERNO", OracleType.NVarChar).Value = dwABPOS["WORDERNO"].ToString().Trim();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    string I;
                    string WMTR;
                    foreach (DataRow dw in dsResult.Tables[0].Rows)
                    {

                        I = dw["ABPERLKZ"].ToString().Trim();
                        if (I == "J")
                        {
                            dwABPOS["REMARK"] += "织造完缸;";
                        }
                        WMTR = dw["QTY"].ToString().Trim();
                        dwABPOS["WMTR"] = WMTR;

                    }
                }

            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void GetABPR(DataRow dwABPOS)
        {

            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT A.ARWERT/1000 AS PRICE FROM ABPR A "
                    + " WHERE A.ARAFOLGE = '000' AND A.ARAUFKRS||'-'||TO_NUMBER(A.ARAUFLFD)||'-'||TO_NUMBER(A.ARAUFPOS) = :ORDERNO "
                     ;
                orlReadCommand.Parameters.Add("ORDERNO", OracleType.NVarChar).Value = dwABPOS["FORDERNO"].ToString().Trim();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    foreach (DataRow dw in dsResult.Tables[0].Rows)
                    {

                        dwABPOS["PRICE"] = dw["PRICE"].ToString().Trim();

                    }
                }

            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        public DataSet BuildWBBST(DataRow dwUI)
        {
            string progress = "查询完成";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                EventDispatcher.Instance.RaiseStatus("查询开始", StatusEventType.StartProgress);
                setResult = GetRawData(dwUI);
                setReturn.Merge(setResult);                
                EventDispatcher.Instance.RaiseStatus("查询完成", StatusEventType.StopProgress);
             
            }
            catch (Exception ex)
            {
                Tracer.Error(ABPOS + ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}